package com.evil.common.file.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.util.PoiCssUtils;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IORuntimeException;
import cn.hutool.core.io.file.FileNameUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ZipUtil;
import com.aspose.cells.License;
import com.aspose.cells.SaveFormat;
import com.evil.common.core.enums.BaseEnum;
import com.evil.common.core.exception.BusinessException;
import com.evil.common.file.constant.PoiConstant;
import com.evil.common.file.enums.FileRCodeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.BaseFormulaEvaluator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import javax.validation.constraints.Min;
import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Consumer;

/**
 * poi 工具类
 *
 * @author liyang
 * @date 2022-08-08 00:00
 */
@Slf4j
public class PoiUtil {

    /**
     * excel2003扩展名
     */
    public static final String EXCEL03_EXTENSION = ".xls";
    /**
     * excel2007扩展名
     */
    public static final String EXCEL07_EXTENSION = ".xlsx";
    /**
     * docx
     */
    public static final String DOCX_EXTENSION = ".docx";
    /**
     * docx
     */
    public static final String PDF_EXTENSION = ".pdf";
    /**
     * 导出 请求key
     */
    public final static String DOWN_FILE_HEADER_NAME = "filename";
    /**
     * 下载文件
     */
    public final static String CONTENT_DISPOSITION_HEADER_NAME = "Content-Disposition";
    /**
     * 导出分隔符
     */
    public final static String IMP_EXP_SEPARATORS = "、";

    // region Excel

    /**
     * 创建单元格
     *
     * @param row       行
     * @param columnNum 列
     * @param cellStyle 样式
     * @param value     值
     * @return Cell
     */
    public static Cell createCell(Row row, int columnNum, CellStyle cellStyle, Object value) {
        Cell cell = row.createCell(columnNum);
        cell.setCellStyle(cellStyle);
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        }
        return cell;
    }

    /**
     * 获取行指定列，如果不存在会创建
     *
     * @param row row
     * @param col col
     * @return cell
     */
    public static Cell getCell(Row row, int col) {
        Cell cell = row.getCell(col);
        if (null == cell) {
            cell = row.createCell(col);
        }
        return cell;
    }

    /**
     * 获取单元格 值
     *
     * @param cell cell
     * @return Object
     */
    public static Object getCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case NUMERIC:
                // 防止double科学计数法
                return BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
            default:
                try {
                    return cell.getStringCellValue();
                } catch (Exception ignored) {
                    return null;
                }
        }
    }

    /**
     * 获取 对用字母
     *
     * @param cellNum cellNum
     * @return String
     */
    public static String getCellNumStr(int cellNum) {
        StringBuilder temp = new StringBuilder();
        double i = Math.floor(Math.log(25.0 * (cellNum) / 26.0 + 1) / Math.log(26)) + 1;
        if (i > 1) {
            double sub = cellNum - 26 * (Math.pow(26, i - 1) - 1) / 25;
            for (double j = i; j > 0; j--) {
                temp.append((char) (sub / Math.pow(26, j - 1) + 65));
                sub = sub % Math.pow(26, j - 1);
            }
        } else {
            temp.append((char) (cellNum + 65));
        }
        return temp.toString();
    }

    /**
     * 获取单元格样式
     *
     * @param cell cell
     * @return CellStyle
     */
    public static CellStyle getCellStyle(Cell cell) {
        return null == cell.getCellStyle() ? cell.getSheet().getWorkbook().createCellStyle() : cell.getCellStyle();
    }

    /**
     * 添加错误性 批注
     *
     * @param cell  cell
     * @param msg   msg
     * @param scale scale
     */
    public static void addErrorComment(Cell cell, String msg, @Min(value = 1, message = "scale min-value is 1") int scale) {
        Sheet sheet = cell.getSheet();
        Drawing<?> p = sheet.createDrawingPatriarch();
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();

        Comment comment = null;
        Short color = null;
        RichTextString richTextString = null;
        if (sheet instanceof HSSFSheet) {
            // 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
            comment = p.createCellComment(new HSSFClientAnchor(rowIndex, columnIndex, rowIndex, columnIndex, (short) 0, 0, (short) scale, scale));
            color = HSSFColor.HSSFColorPredefined.RED.getColor().getIndex();

            richTextString = new HSSFRichTextString(msg);
        } else if (sheet instanceof XSSFSheet) {
            // 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
            comment = p.createCellComment(new XSSFClientAnchor(rowIndex, columnIndex, rowIndex, columnIndex, (short) 0, 0, (short) scale, scale));
            color = PoiCssUtils.parseColor("RED").getIndex();

            richTextString = new XSSFRichTextString(msg);
        }
        if (null != comment) {
            // 输入批注信息
            comment.setString(richTextString);
            cell.setCellComment(comment);
            CellStyle cellStyle = PoiUtil.getCellStyle(cell);
            cellStyle.setFillForegroundColor(color);
        }
    }

    /**
     * 获取公式运算器
     *
     * @param workbook workbook
     * @return BaseFormulaEvaluator
     */
    public static BaseFormulaEvaluator getFormulaEvaluator(Workbook workbook) {
        BaseFormulaEvaluator baseFormulaEvaluator;
        if (workbook instanceof XSSFWorkbook) {
            baseFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        } else if (workbook instanceof HSSFWorkbook) {
            baseFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        } else {
            throw new BusinessException(BaseEnum.ERROR_PARAMETER);
        }
        return baseFormulaEvaluator;
    }

    /**
     * 获取坐标单元格合并行数
     *
     * @param sheet  sheet
     * @param row    row
     * @param column column
     * @return List
     */
    public static int getMergeIndex(Sheet sheet, int row, int column) {
        return sheet.getMergedRegions().stream()
                .filter(e -> e.isInRange(row, column))
                .findFirst().map(e -> e.getLastRow() - e.getFirstRow() + 1).orElse(1);
    }

    /**
     * 文件名称后缀 是否是03版Excel
     *
     * @param fileName fileName
     * @return boolean
     */
    public static boolean isExcel03(String fileName) {
        return fileName.toLowerCase().endsWith(PoiUtil.EXCEL03_EXTENSION.toLowerCase());
    }

    /**
     * 文件名称后缀 是否是07版Excel
     *
     * @param fileName fileName
     * @return boolean
     */
    public static boolean isExcel07(String fileName) {
        return fileName.toLowerCase().endsWith(PoiUtil.EXCEL07_EXTENSION.toLowerCase());
    }

    /**
     * 导出数据到Excel
     *
     * @param list      list
     * @param title     title
     * @param sheetName sheetName
     * @param pojoClass pojoClass
     * @param fileName  fileName
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName) {
        // 判断导出数据是否为空
        if (list == null) {
            list = new ArrayList<>();
        }

        // 获取导出参数
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setHeight((short) 15);

        // 处理 excel 版本文件 , 默认 07 版本
        if (PoiUtil.isExcel03(fileName)) {
            exportParams.setType(ExcelType.HSSF);
        } else if (PoiUtil.isExcel07(fileName)) {
            exportParams.setType(ExcelType.XSSF);
        }

        // 设置导出样式
        exportParams.setStyle(ExcelStyleUtil.class);

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (ObjectUtil.isNotNull(workbook)) {
            PoiUtil.outputStreamExcel(workbook, fileName, true);
        }
    }

    /**
     * 创建工作簿样式
     *
     * @param workbook 工作簿
     * @param fontSize 字体大小
     * @param flag     是否加粗
     * @param status   垂直居中
     * @return 单元格样式
     */
    public static CellStyle createdCellStyle(Workbook workbook, short fontSize, boolean flag, boolean status) {
        CellStyle style = workbook.createCellStyle();
        // 是否水平居中
        if (status) {
            // 水平居中
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建字体
        Font font = workbook.createFont();
        // 是否加粗字体
        if (flag) {
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontSize);
        // 加载字体
        style.setFont(font);
        return style;
    }

    /**
     * MultipartFile转Workbook
     *
     * @param file file
     * @return Workbook
     */
    public static Workbook multipartFileToWorkbook(MultipartFile file) {
        if (StringUtils.isBlank(file.getOriginalFilename())) {
            throw new BusinessException(BaseEnum.ERROR_PARAMETER, "文件名称不能为空");
        }
        try {
            if (PoiUtil.isExcel03(file.getOriginalFilename())) {
                return new HSSFWorkbook(file.getInputStream());
            } else if (PoiUtil.isExcel07(file.getOriginalFilename())) {
                return new XSSFWorkbook(file.getInputStream());
            }
        } catch (IOException ignored) {
        }
        throw new BusinessException(BaseEnum.RUN_ERROR);
    }

    /**
     * Workbook转MultipartFile
     *
     * @param workbook workbook
     * @param fileName fileName
     * @return MultipartFile
     */
    public static MultipartFile workbookToMultipartFile(Workbook workbook, String fileName, boolean supplySuffix) {
        String suffix;
        String versionsType;
        if (workbook instanceof HSSFWorkbook) {
            suffix = EXCEL03_EXTENSION;
            versionsType = PoiConstant.EXCEL_03_CONTENT_TYPE;
        } else if (workbook instanceof XSSFWorkbook) {
            suffix = EXCEL07_EXTENSION;
            versionsType = PoiConstant.EXCEL_07_CONTENT_TYPE;
        } else {
            throw new BusinessException(FileRCodeEnum.EXPORT_FILE_TYPE);
        }
        // 业务逻辑，给HSSFWorkbook添加数据
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
        fileName = supplySuffix ? fileName + suffix : fileName;
        return new MockMultipartFile(fileName, fileName, versionsType, bos.toByteArray());
    }

    /**
     * Workbook转PDF文件
     *
     * @param workbook workbook
     * @param filename filename
     */
    public static MultipartFile preview(Workbook workbook, String filename) {
        // 检查POI转换文件类型 是否可用
        PoiUtil.checkLicense();
        // 业务逻辑，给HSSFWorkbook添加数据
        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            PoiUtil.workbookToInputSteam(workbook, bis -> {
                try {
                    new com.aspose.cells.Workbook(bis).save(bos, SaveFormat.PDF);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new BusinessException(BaseEnum.RUN_ERROR);
                }
            });

            filename = FileNameUtil.mainName(filename) + PDF_EXTENSION;
            return new MockMultipartFile(filename, filename, PoiConstant.PDF_CONTENT_TYPE, bos.toByteArray());
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
    }

    /**
     * Workbook转InputStream
     *
     * @param workbook workbook
     */
    public static void workbookToInputSteam(Workbook workbook, Consumer<ByteArrayInputStream> handle) {
        AtomicReference<ByteArrayInputStream> atomicReferences = new AtomicReference<>();
        PoiUtil.workbookToOutSteam(workbook, bos -> atomicReferences.set(new ByteArrayInputStream(bos.toByteArray())));
        if (Objects.nonNull(atomicReferences.get())) {
            try (ByteArrayInputStream bis = atomicReferences.get()) {
                handle.accept(bis);
            } catch (Exception e) {
                e.printStackTrace();
                throw new BusinessException(BaseEnum.RUN_ERROR);
            }
        }
    }

    /**
     * 使用结束 必须close
     *
     * @param workbook workbook
     */
    public static void workbookToOutSteam(Workbook workbook, Consumer<ByteArrayOutputStream> handle) {
        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            workbook.write(bos);
            handle.accept(bos);
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
    }

    /**
     * Workbook转File
     *
     * @param workbook workbook
     */
    public static File workbookToFile(Workbook workbook, String fileName) {
        File folder = FileUtil.mkdir(FileUtil.getTmpDirPath() + System.currentTimeMillis());
        String path = folder.getPath() + "\\" + fileName + PoiUtil.getWorkbookSuffix(workbook);
        File file = FileUtil.newFile(path);
        try (OutputStream os = new FileOutputStream(file)) {
            workbook.write(os);
            return file;
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
    }

    /**
     * file转MultipartFile
     *
     * @param file file
     * @return MultipartFile
     * @throws Exception e
     */
    public static MultipartFile fileToMultipartFile(File file, String contentType) throws Exception {
        if (!file.isFile() || !file.exists()) {
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
        String fileName = FileNameUtil.mainName(file.getName());
        String name = FileNameUtil.getName(file.getPath());
        if (StringUtils.isBlank(fileName)) {
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
        BufferedInputStream inputStream = FileUtil.getInputStream(file);
        return new MockMultipartFile(fileName, name, contentType, inputStream);
    }

    /**
     * 压缩工作簿到文件夹
     *
     * @param folder    文件夹
     * @param workbook  工作簿
     * @param excelName 工作簿文件名
     * @param clear     是否清理文件夹
     * @return zip
     */
    public static File zipWorkbook(File folder, Workbook workbook, String excelName, boolean clear) {
        String suffix;
        if (workbook instanceof HSSFWorkbook) {
            suffix = EXCEL03_EXTENSION;
        } else if (workbook instanceof XSSFWorkbook) {
            suffix = EXCEL07_EXTENSION;
        } else {
            throw new BusinessException(FileRCodeEnum.EXPORT_FILE_TYPE);
        }
        // 创建临时文件
        File file = new File(String.format("%s/%s%s", folder.getPath(), excelName, suffix));
        try {
            // 把导出数据写到临时文件
            OutputStream out = new FileOutputStream(file);
            workbook.write(out);
            // 把附件和导出数据一起打包成压缩包
            return ZipUtil.zip(folder);
        } catch (Exception e) {
            log.error("【导出错误】：{}", e.getMessage());
            throw new BusinessException(BaseEnum.RUN_ERROR);
        } finally {
            // 清理缓存
            if (clear) {
                try {
                    FileUtil.del(folder);
                } catch (IORuntimeException ignored) {
                }
            }
        }
    }

    /**
     * 导出文件,释放资源
     *
     * @param workbook 文件
     * @param filename 文件名称
     */
    public static void outputStreamExcel(Workbook workbook, String filename, boolean supplySuffix) {
        String suffix;
        String versionsType;
        if (workbook instanceof HSSFWorkbook) {
            suffix = EXCEL03_EXTENSION;
            versionsType = PoiConstant.EXCEL_03_CONTENT_TYPE;
        } else if (workbook instanceof XSSFWorkbook) {
            suffix = EXCEL07_EXTENSION;
            versionsType = PoiConstant.EXCEL_07_CONTENT_TYPE;
        } else {
            throw new BusinessException(FileRCodeEnum.EXPORT_FILE_TYPE);
        }

        Consumer<OutputStream> export = os -> {
            try {
                workbook.write(os);
            } catch (IOException e) {
                throw new BusinessException(BaseEnum.RUN_ERROR);
            }
        };
        filename += (supplySuffix ? suffix : "");
        ExportUtil.export(export, versionsType, filename);
    }

    /**
     * 获取 Workbook 文件名后缀
     *
     * @param workbook workbook
     * @return String
     */
    public static String getWorkbookSuffix(Workbook workbook) {
        if (workbook instanceof HSSFWorkbook) {
            return EXCEL03_EXTENSION;
        } else if (workbook instanceof XSSFWorkbook) {
            return EXCEL07_EXTENSION;
        } else {
            throw new BusinessException(FileRCodeEnum.EXPORT_FILE_TYPE);
        }
    }
    // endregion

    // region Word

    /**
     * doc转MultipartFile
     *
     * @param doc      doc
     * @param fileName fileName
     * @return MultipartFile
     */
    public static MultipartFile docToMultipartFile(XWPFDocument doc, String fileName, boolean supplySuffix) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            doc.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
        fileName = supplySuffix ? fileName + DOCX_EXTENSION : fileName;
        return new MockMultipartFile(fileName, fileName, PoiConstant.WORD_CONTENT_TYPE, bos.toByteArray());
    }

    /**
     * XWPFDocument转PDF文件
     *
     * @param doc      doc
     * @param filename filename
     */
    public static MultipartFile preview(XWPFDocument doc, String filename) {
        // 检查POI转换文件类型 是否可用
        PoiUtil.checkLicense();
        // 业务逻辑，给HSSFWorkbook添加数据
        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            PoiUtil.wordToInputSteam(doc, bis -> {
                try {
                    new com.aspose.words.Document(bis).save(bos, SaveFormat.PDF);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new BusinessException(BaseEnum.RUN_ERROR);
                }
            });

            filename = FileNameUtil.mainName(filename) + PDF_EXTENSION;
            return new MockMultipartFile(filename, filename, PoiConstant.PDF_CONTENT_TYPE, bos.toByteArray());
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
    }

    /**
     * word转InputStream
     *
     * @param doc doc
     */
    public static void wordToInputSteam(XWPFDocument doc, Consumer<ByteArrayInputStream> handle) {
        AtomicReference<ByteArrayInputStream> atomicReferences = new AtomicReference<>();
        PoiUtil.wordToOutSteam(doc, bos -> atomicReferences.set(new ByteArrayInputStream(bos.toByteArray())));
        if (Objects.nonNull(atomicReferences.get())) {
            try (ByteArrayInputStream bis = atomicReferences.get()) {
                handle.accept(bis);
            } catch (Exception e) {
                e.printStackTrace();
                throw new BusinessException(BaseEnum.RUN_ERROR);
            }
        }
    }

    /**
     * word转OutputStream
     *
     * @param doc doc
     */
    public static void wordToOutSteam(XWPFDocument doc, Consumer<ByteArrayOutputStream> handle) {
        try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            doc.write(bos);
            handle.accept(bos);
        } catch (IOException e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
    }

    /**
     * 导出文件,释放资源
     *
     * @param doc          文件
     * @param filename     文件名称
     * @param supplySuffix 文件名是否补充后缀
     */
    public static void exportWord(XWPFDocument doc, String filename, boolean supplySuffix) {
        // 导出
        Consumer<OutputStream> export = os -> {
            try {
                doc.write(os);
            } catch (IOException e) {
                throw new BusinessException(BaseEnum.RUN_ERROR);
            }
        };
        filename += (supplySuffix ? DOCX_EXTENSION : "");
        ExportUtil.export(export, PoiConstant.WORD_CONTENT_TYPE, filename);
    }
    // endregion

    // region checkLicense

    /**
     * 检查POI转换文件类型 是否可用
     */
    public static void checkLicense() {
        try {
            InputStream is = PoiUtil.class.getClassLoader().getResourceAsStream("license.xml"); //// license.xml这个文件你放在静态文件资源目录下就行了
            License aposeLic = new License();
            aposeLic.setLicense(is);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException(BaseEnum.RUN_ERROR);
        }
    }
    // endregion
}
